﻿using System;

namespace Controller
{
    class DBFacade     // DB facaden er skrevet af Tien, Trang, Rud, Per og Enemark
    {
        #region Fields
        public DBHelper Db;
        public bool IsConnected = false;
        private readonly MyController _myController;
        #endregion

        public DBFacade(MyController myController, string dataSource, string username, string password)
        {
            //Db = new DBHelper("ealdb1.eal.local", "ejl05_usr", "Baz1nga5");
            Db = new DBHelper(dataSource, username, password);
            Db.Connect();
            IsConnected = Db.IsConnected(); //Check connection
            _myController = myController;
        }

        #region Load data
        public void LoadAllData() //Load data - Per, Mads Enemark, Mads Ruud
        {
            LoadRid();
            LoadReport();
            LoadFeature();
            LoadData();
            LoadOperator();
            LoadOperatorSession();
            LoadNote();
        }
        public void LoadRid() // LOAD RID - Per, Mads Enemark, Mads Ruud
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("GetRids");
                while (Db.data.Read())
                {
                    long rid = (long)Db.data["rid"];
                    DateTime createDate = (Db.data["createdate"] == DBNull.Value) ? DateTime.Today : Convert.ToDateTime(Db.data["createdate"]);
                    DateTime updateDate = (Db.data["updatedate"] == DBNull.Value) ? DateTime.Today : Convert.ToDateTime(Db.data["updatedate"]);
                    bool hidden = (bool)Db.data["hidden"];
                    bool deleted = (bool)Db.data["deleted"];
                    bool disabled = (bool)Db.data["disabled"];
                    string syncUUId = Db.data["syncuuid"].ToString();
                    string entity = Db.data["entity"].ToString();
                    _myController.AddRid(rid, entity, createDate, updateDate, deleted, hidden, disabled, syncUUId);
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        public void LoadReport() // LOAD REPORT - Per, Mads Enemark, Mads Ruud
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("GetReports");
                while (Db.data.Read())
                {
                    long rid = (long)Db.data["rid"];
                    int reportId = (int)Db.data["id"];
                    string serial = Db.data["serial"].ToString();
                    string mident = Db.data["mident"].ToString();
                    string model = Db.data["model"].ToString();
                    string mpart = Db.data["mpart"].ToString();
                    string reportfilename = Db.data["reportfilename"].ToString();
                    _myController.AddReport(reportId, rid, serial, model, mident, mpart, reportfilename);
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        public void LoadFeature() //Load Feature - Trang
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("GetFeatures");
                while (Db.data.Read())
                {
                    int id = (int)Db.data["id"];
                    string ident = Db.data["ident"].ToString();
                    string name = Db.data["name"].ToString();
                    string ctrl = Db.data["ctrl"].ToString();
                    double nominal = double.Parse(Db.data["nominal"].ToString());
                    double tolpos = Convert.ToDouble(Db.data["tolpos"]);
                    double tolneg = Convert.ToDouble(Db.data["tolneg"]);

                    _myController.AddFeature(id, ident, name, ctrl, nominal, tolpos, tolneg);
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        public void LoadData() //Load data - Trang
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("GetDatas");
                while (Db.data.Read())
                {
                    int id = (int)Db.data["id"];
                    int fbmrid = (int)Db.data["fbmrid"];
                    int fbmfid = (int)Db.data["fbmfid"];
                    double measured = Convert.ToDouble(Db.data["measured"]);
                    double deviation = Convert.ToDouble(Db.data["deviation"]);
                    bool test = (bool)Db.data["test"];
                    _myController.AddData(id, fbmrid, fbmfid, measured, deviation, test);
                }

            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        public void LoadOperator() //Load operator - Per, Mads Enemark, Mads Ruud, Tien
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("GetOperators");
                while (Db.data.Read())
                {
                    string initials = Db.data["initials"].ToString();
                    string pass = Db.data["pass"].ToString();
                    bool isEnabled;
                    if (Db.data["isEnabled"].ToString() == "True")
                        isEnabled = true;
                    else
                        isEnabled = false;

                    _myController.AddOperator(initials, pass, isEnabled);
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        public void LoadOperatorSession() //Tien - 29. maj 2013
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("GetOperatorSessions");
                while (Db.data.Read())
                {
                    string initials = Db.data["initials"].ToString();
                    DateTime startTime = (Db.data["start_time"] == DBNull.Value) ? DateTime.Today : Convert.ToDateTime(Db.data["start_time"]);
                    DateTime endTime = (Db.data["end_time"] == DBNull.Value) ? DateTime.Today :
                    Convert.ToDateTime(Db.data["end_time"]);
                    _myController.AddOperatorSession(initials, startTime, endTime);
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        public void LoadNote() //Trang
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("GetNote");
                while (Db.data.Read())
                {
                    int did = (int)Db.data["did"];
                    double newValue = Convert.ToDouble(Db.data["newvalue"]);
                    double oldValue = Convert.ToDouble(Db.data["oldvalue"]);
                    string ndescription = (string)Db.data["ndescription"];
                    DateTime nupdatedate = (DateTime)Db.data["nupdatedate"];
                    string initials = Db.data["initials"].ToString();
                    _myController.AddNote(did, newValue, oldValue, ndescription, nupdatedate, _myController.operatorCollection.Find(initials));
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        #endregion

        public long GetLastRid()// Hent seneste rid fra databasen: det skal bruges til at tjekke på om der er kommet nyt
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("GetLatestRid");

                long rid = 0;
                while (Db.data.Read())
                    rid = (long)Db.data["rid"];

                return rid;
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }

        // The following region is used to load LATEST reports, features and data as well as rids:
        // Written by Per Leth Andersen
        #region Load LATEST 
        public void LoadLatest(long rid)
        {
            LoadLatestRid();
            LoadLatestReport(rid + 1);
            LoadLatestFeatures(rid + 1);
            LoadLatestData(rid + 1);
        }
        private void LoadLatestRid() // Rid:
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("GetLatestRid");
                while (Db.data.Read())
                {
                    long rid_ = (long)Db.data["rid"];
                    DateTime createDate = (Db.data["createdate"] == DBNull.Value) ? DateTime.Today : Convert.ToDateTime(Db.data["createdate"]);
                    DateTime updateDate = (Db.data["updatedate"] == DBNull.Value) ? DateTime.Today : Convert.ToDateTime(Db.data["updatedate"]);
                    bool hidden = (bool)Db.data["hidden"];
                    bool deleted = (bool)Db.data["deleted"];
                    bool disabled = (bool)Db.data["disabled"];
                    string syncUUId = Db.data["syncuuid"].ToString();
                    string entity = Db.data["entity"].ToString();
                    _myController.AddRid(rid_, entity, createDate, updateDate, deleted, hidden, disabled, syncUUId);
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        private void LoadLatestReport(long rid) // Report:
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("SelectLatestReport", "@rid", rid);
                while (Db.data.Read())
                {
                    long rid_ = (long)Db.data["rid"];
                    int reportId = (int)Db.data["id"];
                    string serial = Db.data["serial"].ToString();
                    string mident = Db.data["mident"].ToString();
                    string model = Db.data["model"].ToString();
                    string mpart = Db.data["mpart"].ToString();
                    string reportfilename = Db.data["reportfilename"].ToString();
                    _myController.AddReport(reportId, rid_, serial, model, mident, mpart, reportfilename);
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        private void LoadLatestFeatures(long rid) // Feature:
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("SelectLatestFeature", "@rid", rid);
                while (Db.data.Read())
                {
                    int id = (int)Db.data["id"];
                    string ident = Db.data["ident"].ToString();
                    string name = Db.data["name"].ToString();
                    string ctrl = Db.data["ctrl"].ToString();
                    double nominal = double.Parse(Db.data["nominal"].ToString());
                    double tolpos = Convert.ToDouble(Db.data["tolpos"]);
                    double tolneg = Convert.ToDouble(Db.data["tolneg"]);
                    _myController.AddFeature(id, ident, name, ctrl, nominal, tolpos, tolneg);
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        private void LoadLatestData(long rid) // Data:
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("SelectLatestData", "@rid", rid);

                while (Db.data.Read())
                {
                    int id = (int)Db.data["id"];
                    int fbmrid = (int)Db.data["fbmrid"];
                    int fbmfid = (int)Db.data["fbmfid"];
                    double measured = Convert.ToDouble(Db.data["measured"]);
                    double deviation = Convert.ToDouble(Db.data["deviation"]);
                    bool test = (bool)Db.data["test"];
                    _myController.AddData(id, fbmrid, fbmfid, measured, deviation, test);
                }
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        #endregion

        #region Add- and update operator
        public void AddOperator(string initials, string password, bool isEnabled) //Tien - 13. maj, 29. maj
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("CreateOperator", "@initials", initials, "@pass", password, "@isEnabled", isEnabled);
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        public void UpdateOperator(string initials, string pass, bool isEnabled)
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("UpdateOperator", "@initials", initials, "@pass", pass, "@isEnabled", isEnabled);
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        #endregion

        #region add- and update operator session
        public void AddOperatorSession(string initials, DateTime startTime, DateTime endTime)
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("CreateOperatorSession", "@initials", initials, "@start_time", startTime, "@end_time", endTime);
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }

        public void UpdateOperatorSession(string initials, DateTime startTime, DateTime endTime)
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("UpdateOperatorSession", "@initials", initials, "@start_time", startTime, "@end_time", endTime);
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        #endregion

        #region add- and update note
        public void AddNote(int did, double newValue, double oldValue, string nDesciption, DateTime nUpdateDate, string initials)
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("InsertNote", "@did", did, "@newvalue", newValue, "@oldvalue", oldValue, "@ndescription", nDesciption, "@nupdatedate", nUpdateDate, "@initials", initials);
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        public void UpdateNote(int did, double newvalue, string ndescription, DateTime nupdatedate, string initials) //Trang
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("UpdateNote", "@did", did, "@newvalue", newvalue, "@ndescription", ndescription, "@nupdatedate", nupdatedate, "@initials", initials);
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
        #endregion

        public void UpdateData(int did, double newvalue, double deviation, bool test)//Update fmbdata after changing to newvalue - Trang
        {
            try
            {
                Db.Open();
                Db.SelectDataSP("UpdateData", "@did", did, "@newvalue", newvalue, "@deviation", deviation, "@test", test);
            }
            catch (Exception e) { throw e; }
            finally { Db.Close(); }
        }
    }
}